import time
from HLog import HLog
import configparser
import os
import shutil
import csv
from dataAnlysis import headMain
from package import const, twanalysis, thanalysis
from package.MysqlHelp import _MySqlHelper
import datetime
import zipfile
import gol
import decimal
import time
from HLog import HLog
import configparser
import os
import shutil
import csv
from dataAnlysis import headMain
from package import const, twanalysis, thanalysis
from package.MysqlHelp import _MySqlHelper
import datetime
import zipfile
import gol
import decimal

mysql = _MySqlHelper()
dictc = {}


# 清空表数据14
def removeDataBase(name):
    global mysql
    gol.set_value('dbName', name)
    sql = "select CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,';') from information_schema" \
          ".tables " \
          "where TABLE_SCHEMA in ('" + name + "');"
    listSql = mysql.select(sql)

    inSqlList = []
    for t in listSql:
        for sqlTmp in t:
            ## Eric 2021
            ## if (sqlTmp.find("fare_statistics") >= 0) or (sqlTmp.find("big_data_statistics") >= 0):
            if (sqlTmp.find("fare_statistics") >= 0):
                    pass
            else:
                inSqlList.append(sqlTmp)

    mysql.executeAll(inSqlList)


def removeLastDayFromSql():
    removeDataBase("transaction_datalc")
    removeDataBase("transaction_datasc")


def del_file(path):
    ls = os.listdir(path)
    for i in ls:
        c_path = os.path.join(path, i)
        if os.path.isdir(c_path):
            del_file(c_path)
        else:
            os.remove(c_path)


def add_1(binary_inpute):  # 二进制编码加1
    _, out = bin(int(binary_inpute, 2) + 1).split("b")
    return out


def reverse(binary_inpute):  # 取反操作
    binary_inpute = list(binary_inpute)
    binary_out = binary_inpute
    for epoch, i in enumerate(binary_out):
        if i == "0":
            binary_out[epoch] = "1"
        else:
            binary_out[epoch] = "0"
    return "".join(binary_out)


class DownLoadTools(object):

    def __init__(self):
        rq = time.strftime('%Y%m%d', time.localtime(time.time()))
        self.log = HLog("main", rq)
        self.log.info('开始运行.......')
        self.currentPath = "./alysisFile/" + str(datetime.datetime.now().weekday()) + "day"
        gol._init()
        self.cf = configparser.ConfigParser()
        self.cf.read("./conf/deal.conf")

        self.bookdate = self.cf.get('option', 'bookdate')
        if self.bookdate == "":
            yesterday = datetime.datetime.today() + datetime.timedelta(-1)
            yesterday_format = yesterday.strftime('%Y%m%d')
            self.bookdate = yesterday_format

    def anlysisData(self):

        ftpList: list = self.cf.options("source_file")
        for key in ftpList:
            self.onecAnysis(key)

    def onecAnysis(self, key):
        tmpDir = self.cf.get("option", "tmpDir")
        sourceRoot = self.cf.get("option", "source_file")
        cfgStr = self.cf.get("source_file", key)
        attributes = cfgStr.split(',')
        sCurrentPath: str = sourceRoot + attributes[0]
        # 解压源文件zip
        for root, ds, fs in os.walk(sCurrentPath):
            if root == sCurrentPath:
                for f in fs:
                    if f.find(".zip") >= 0:
                        # if f.find(self.bookdate):
                        self.log.info(f)
                        zipf = None
                        try:
                            zipf = zipfile.ZipFile(root + f, 'r')
                        except Exception as e:

                            isExists = os.path.exists(tmpDir + self.bookdate)
                            if not isExists:
                                os.makedirs(tmpDir + self.bookdate)
                            shutil.copy(root + f, tmpDir + self.bookdate)
                            os.remove(root + f)

                            self.log.error("error" + f + str(e))
                        if zipf is None:
                            self.log.error(f)
                            continue
                        for file in zipf.namelist():
                            try:
                                zipf.extract(file, sCurrentPath)
                            except Exception as e:
                                self.log.error("erro" + f + str(e))
                        zipf.close()

                        isExists = os.path.exists(tmpDir + self.bookdate)
                        if not isExists:
                            os.makedirs(tmpDir + self.bookdate)
                        shutil.copy(root + f, tmpDir + self.bookdate)
                        os.remove(root + f)

        aCurrentPath = self.currentPath + "/" + attributes[0]
        # 清空解析后csv并创建
        shutil.rmtree(aCurrentPath)
        os.mkdir(aCurrentPath)

        dbName = ""
        if attributes[3] == "sc":
            gol.set_value('dbName', "transaction_datasc")
            dbName = "transaction_datasc"
        elif attributes[3] == "lc":
            gol.set_value('dbName', "transaction_datalc")
            dbName = "transaction_datalc"

        self.log.info("处理" + sCurrentPath)
        if os.path.exists(sCurrentPath):
            eval(attributes[5])(sCurrentPath, aCurrentPath, attributes[4], attributes[3], dbName)

    def statisticsToSql(self):
        global mysql
        with open('conf/sclc.csv', 'r', encoding='UTF-8') as f:
            reader = csv.reader(f)
            count = 0
            for row in reader:

                if count < 1:
                    count = count + 1
                    continue

                columns = ""
                columnsList = []
                for index in range(len(row)):
                    if index == 0:
                        continue
                    column = row[index]
                    if column != "" and index < (len(row) - 2):
                        columns = columns + column + ","
                        columnsList.append(index)

                if row[len(row) - 2] == "":
                    cardid = "*"
                else:
                    cardid = row[len(row) - 2]
                columnsList.append(13)

                if row[len(row) - 1] == "":
                    txnamt = " "
                else:
                    txnamt = ",SUM(case when %s > 4000000000 then  - (%s^4294967295+ 1) " \
                             "ELSE %s END) AS txnamtall" % (row[len(row) - 1], row[len(row) - 1], row[len(row) - 1])
                    columnsList.append(14)

                # between = self.cf.get('mysql', 'statistics_between')
                # if between == "":
                #     yesterday = datetime.today() + timedelta(-1)
                #     yesterday_format = yesterday.strftime('%Y%m%d')
                #     between = row[1] + " BETWEEN " + yesterday_format + " AND " + yesterday_format + " "
                # else:
                #     between = row[1] + " " + between

                if row[0] == "txnproductpursecompensationfare":
                    print(1)

                sql = "SELECT " + columns + "COUNT(" + cardid + ") AS carnum" + txnamt + " FROM " + \
                      row[0] + " GROUP BY " + columns[:-1]
                re = mysql.select(sql)

                sqlColumn = ["bookdate", "occurrence_date", "lineid", "station_number", "paymethod", "equipmenttype",
                             "station_equipment_number", "posoperid", "udtype", "udsubtype", "producttype",
                             "crdmKnd", "crdSKnd", "carnum", "txnamtall"]
                columnStr = ""
                temIndex = 0
                for sIndex, columnIndex in enumerate(columnsList):
                    columnStr = columnStr + sqlColumn[columnIndex] + ","
                    if sqlColumn[columnIndex] == "station_equipment_number":
                        temIndex = sIndex

                sqllist = []

                for once in re:
                    vStr = ""
                    for ssindex, v in enumerate(once):
                        # if ssindex == (len(once) - 1):
                        # print(v)

                        # if len(txnamt) > 5 and ssindex == (len(once) - 1) and int(v) > 4000000000:
                        #     print(v)
                        #     a = int(v)
                        #     b = str(bin(a)).lstrip("0b")
                        #     b = reverse(b)
                        #     b = add_1(b)
                        #     b = -int(b, 2)
                        #
                        #     vStr = vStr + "\"" + str(b) + "\","
                        #     continue
                        if ssindex == temIndex and int(v) < 10:
                            vStr = vStr + "\"0" + str(v) + "\","
                        else:
                            vStr = vStr + "\"" + str(v) + "\","
                    sql = "INSERT INTO big_data_statistics ( bookdate," + columnStr[
                                                                          :-1] + ") VALUES (" + self.bookdate + "," + vStr[
                                                                                                                      :-1] + ")"
                    sqllist.append(sql)
                mysql.executeAll(sqllist)
                count = count + 1

    def finalStatistics(self, name):
        global mysql
        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,SUM(carnum),0,0,0,0,SUM(txnamtall),0,0,0,0 " \
              "FROM big_data_statistics WHERE ((udtype = 8 AND udsubtype =15 ) OR (udtype = 3 AND udsubtype = 3 ) OR (udtype = 3 AND udsubtype = 2 ) OR (udtype = 3 AND udsubtype = 1 ) OR (udtype = 3 AND udsubtype = 121 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd"
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,SUM(carnum),0,0,0,SUM(txnamtall),0,0,0 " \
              "FROM big_data_statistics WHERE ((udtype = 8 AND udsubtype =14 ) OR (udtype = 3 AND udsubtype = 12 ) OR (udtype = 3 AND udsubtype = 11 ) OR (udtype = 3 AND udsubtype = 10 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd"
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,SUM(carnum),0,0,0,SUM(txnamtall),0 ,0 " \
              "FROM big_data_statistics WHERE ((udtype = 33 AND udsubtype =17 ) OR (udtype = 34 AND udsubtype = 17 ) OR (udtype = 35 AND udsubtype = 17 ) or (udtype = 17 AND udsubtype =33 ) OR (udtype = 17 AND udsubtype = 34 ) OR (udtype = 17 AND udsubtype = 33 ) OR (udtype = 3 AND udsubtype = 120 ) OR (udtype = 3 AND udsubtype = 119 ) OR (udtype = 3 AND udsubtype = 118 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])

        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,SUM(carnum),0,0,0,0,0,0,SUM(txnamtall),0 " \
              "FROM big_data_statistics WHERE ((udtype = 3 AND udsubtype =28 ) OR (udtype = 3 AND udsubtype = 27 ) OR (udtype = 3 AND udsubtype = 26 ) OR (udtype = 1 AND udsubtype = 80 ) OR (udtype = 1 AND udsubtype = 81 )OR (udtype = 80 AND udsubtype = 1 ) OR (udtype = 81 AND udsubtype = 1 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])
        ## Eric 20211113 append '0316','0317','0318','0379','0380','0381'
        """
        sql = "INSERT INTO fare_tmp (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,0,SUM(carnum),0,0,0,0,SUM(txnamtall) " \
              "FROM big_data_statistics WHERE ((udtype = 1 AND udsubtype =33 ) OR (udtype = 1 AND udsubtype = 32 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        """
        sql = "INSERT INTO fare_tmp (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,offset_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall) " \
              "SELECT bookdate,trim(replace(occurrence_date,'\t','')),lineid,CAST(station_number AS SIGNED) as station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd,0,0,0,0,SUM(carnum),0,0,0,0,SUM(txnamtall) " \
              "FROM big_data_statistics WHERE ((udtype = 1 AND udsubtype =33 ) OR (udtype = 1 AND udsubtype = 32 ) OR (udtype = 3 AND udsubtype = 16 ) OR (udtype = 3 AND udsubtype = 17 ) OR (udtype = 3 AND udsubtype = 18 ) OR (udtype = 3 AND udsubtype = 79 ) OR (udtype = 3 AND udsubtype = 80 ) OR (udtype = 3 AND udsubtype = 81 )) AND bookdate = " + self.bookdate + \
              " GROUP BY bookdate,occurrence_date,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,crdmKnd,crdSKnd "
        mysql.executeAll([sql])

        ## Eric 20211116 for sale_txnamtall - offset
        """
        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 2 or lineid = 3 or lineid = 14 or lineid = 37) " \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,case equipmenttype when 7 then 2 when 1 then 1 end,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 7 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid  = 6) " \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-90,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 1 or lineid = 4)" \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-190,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 5 or lineid = 16)" \
                                             " GROUP BY bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])
        """
        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 2 or lineid = 3 or lineid = 14 or lineid = 37 or lineid = 36 ) " \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0)  AS SIGNED),paymethod,case equipmenttype when 7 then 2 when 1 then 1  else equipmenttype end,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number,3,0)) when 3 then  concat('STM',LPAD(station_equipment_number,3,0)) when 7 then concat('BOM',LPAD(station_equipment_number,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number,3,0))end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid  = 6 or lineid  = 9) " \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0)  AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-90,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 1 or lineid = 4)" \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

        sql = "INSERT INTO subway." + name + " (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,refund_carnum,recharge_carnum,supplement_carnum,sale_txnamtall,recharge_txnamtall,supplement_txnamtall,refund_txnamtall,offset_txnamtall,offset_carnum)" \
                                             " SELECT bookdate,tradeDate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),paymethod,equipmenttype,case equipmenttype when 1 then  concat('V',LPAD(station_equipment_number-60,3,0)) when 2 then concat('BOM',LPAD(station_equipment_number-190,3,0)) end ,trim(replace(posoperid,'\t','')),productType,productSubType,sum(sale_carnum - offset_carnum),sum(refund_carnum),sum(recharge_carnum),sum(supplement_carnum),sum(sale_txnamtall - offset_txnamtall),sum(recharge_txnamtall),sum(supplement_txnamtall),sum(refund_txnamtall),sum(offset_txnamtall),sum(offset_carnum) " \
                                             " FROM fare_tmp where bookdate = %s and (lineid = 5 or lineid = 16)" \
                                             " GROUP BY bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType" % (
                  self.bookdate)
        mysql.executeAll([sql])

    def sc_out_statistics(self):
        ## Eric 20211110 append udtype and udsubtype
        """
        sql = "INSERT INTO subway.sc_out_statistics (bookdate,lineid,station_number,udType,udSubType,productType,productSubType,outCount,amount) " \
              "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),udtype,udsubtype,crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
              "FROM transaction_datasc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
              " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
              " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
              " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
              "OR (udtype = 3 AND udsubtype = 92 ) " \
              "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
              "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
              "bookdate = %s GROUP BY bookdate,lineid,station_number,crdmKnd,crdSKnd" % self.bookdate
        """

        sql = "INSERT INTO subway.sc_out_statistics (bookdate,lineid,station_number,udType,udSubType,productType,productSubType,outCount,amount) " \
              "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),udtype,udsubtype,crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
              "FROM transaction_datasc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
              " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
              " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
              " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
              "OR (udtype = 3 AND udsubtype = 92 ) " \
              "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
              "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
              "bookdate = %s GROUP BY bookdate,lineid,station_number,udType,udSubType,crdmKnd,crdSKnd" % self.bookdate

        mysql.executeAll([sql])
        ## Eric append next lc_out_statistics
        sql = "INSERT INTO subway.lc_out_statistics (bookdate,lineid,station_number,udType,udSubType,productType,productSubType,outCount,amount) " \
              "SELECT bookdate,CAST(trim(replace(lineid,'\t',''))AS SIGNED),CAST(LPAD(trim(replace(station_number,'\t','')),2,0) AS SIGNED),udtype,udsubtype,crdmKnd,crdSKnd,SUM(carnum),SUM(txnamtall) " \
              "FROM transaction_datalc.big_data_statistics WHERE ((udtype = 33 AND udsubtype = 13 ) " \
              " OR (udtype = 33 AND udsubtype = 15 ) OR (udtype = 34 AND udsubtype = 13 ) " \
              " OR (udtype = 34 AND udsubtype = 15 ) OR (udtype = 35 AND udsubtype = 13 ) " \
              " OR (udtype = 35 AND udsubtype = 15 ) OR (udtype = 3 AND udsubtype = 93 ) " \
              "OR (udtype = 3 AND udsubtype = 92 ) " \
              "OR (udtype = 3 AND udsubtype = 91 ) OR (udtype = 35 AND udsubtype = 113 ) " \
              "OR (udtype = 3 AND udsubtype = 115 ) OR (udtype = 35 AND udsubtype = 117 )) AND " \
              "bookdate = %s GROUP BY bookdate,lineid,station_number,udType,udSubType,crdmKnd,crdSKnd" % self.bookdate

        mysql.executeAll([sql])

    def update_day_cell(self):
        sql = "update subway.sw_st_stock AS ss " \
              "set ss. AGM_RECOVERY = (select  sum(ifnull(outCount,0)) FROM  subway.sc_out_statistics where bookdate='%s'  and lineid = ss.LINE_CODE and station_number= ss.STATION_CODE and (producttype = '3' or producttype = '1' or producttype = '12' ))," \
              "ss.BOX_OFFICE_SALE = (select  sum(ifnull(sale_carnum,0)) from  subway.sc_fare_statistics where bookdate='%s'  and lineid=ss.LINE_CODE and station_number= ss.STATION_CODE and (producttype = 3 or producttype =1 or producttype = 12 )  and equipmenttype=2)," \
              "ss.TVM_SELL_OUT = (select  sum(ifnull(sale_carnum,0))  from  subway.sc_fare_statistics where bookdate='%s'  and lineid=ss.LINE_CODE and station_number=ss.STATION_CODE and producttype=3  and equipmenttype=1) " \
              "where STATISTICS_DATE='%s' and del_flag=0 and TICKET_TYPE_PARAM_EN='PTDCP'" % (
                  self.bookdate, self.bookdate, self.bookdate, self.bookdate)

        mysql.executeAll([sql])

    def updataToFinal(self):
        global mysql
        gol.set_value('dbName', "subway")

        # 钱箱入账
        self.box5109ToSubway()
        self.log.info("钱箱入账")

        # sw_au_tvm_amount_defference
        self.updateFinalForType231("sc", "2")
        self.updateFinalForType231("lc", "3")
        # self.updateFinalForType231("acc", "4")
        # 不要了 self.update5109For231()

        # sw_au_tvm_box_amount_difference
        self.updateFinalForType232()

        # SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE
        self.updateFinalForType2314("sc", "1")
        self.updateFinalForType2314("lc", "2")
        # self.updateFinalForType2314("acc", "3")

        # sw_au_conductor_hierarchy_amount_difference
        self.updateFinalForType2315("sc_fare_statistics", "lc_fare_statistics", "1")
        # self.updateFinalForType2315("lc_fare_statistics", "acc_fare_statistics", "2")

        # SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE
        # self.updateFinalForType239("sc", 1, 2)
        # self.updateFinalForType239("lc", 1, 3)
        # self.updateFinalForType239("acc", 1, 4)
        self.updateFinalForType239("sc_fare_statistics", "lc_fare_statistics", "2", "3")
        # self.updateFinalForType239("lc_fare_statistics", "acc_fare_statistics", "3", "4")

    def updataToFinalACC(self):
        global mysql
        gol.set_value('dbName', "subway")
        self.updateFinalForType231("acc", "4")
        self.updateFinalForType2314("acc", "3")

        self.updateFinalForType2315("lc_fare_statistics", "acc_fare_statistics", "2")

        # SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE
        self.updateFinalForType239("lc_fare_statistics", "acc_fare_statistics", "3", "4")

    def updateFinalForType231(self, typeName, typeID):

        sqlList = []
        # 叠加
        # 现金差异
        ## Eric RIGHT JOIN 改为 LEFT JOIN
        """ 
        sql = "UPDATE sw_au_tvm_amount_defference a " \
              "JOIN (SELECT id, SUM(sm) sm, SUM(cz) cz, SUM(tk) tk " \
              "FROM (SELECT * " \
              "FROM sw_au_tvm_amount_defference a " \
              "RIGHT JOIN (SELECT bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,sale_txnamtall + supplement_txnamtall AS sm,recharge_txnamtall AS cz,refund_txnamtall AS tk " \
              "FROM %s_fare_statistics " \
              "WHERE equipmenttype = 1 AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10 OR  paymethod is NULL OR paymethod LIKE '%%313000000000000000000000%%') " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              "WHERE a.RECORD_TIME = %s ) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "%s_CASH_SELL_TICKET = b.sm," \
              "%s_CASH_RECHARGE = b.cz," \
              "%s_CASH_RETURN = b.tk " \
              "WHERE a.RECORD_TIME = %s AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 " \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        """
        sql = "UPDATE sw_au_tvm_amount_defference a " \
              "JOIN (SELECT id, SUM(ifnull(sm,0)) sm, SUM(ifnull(cz,0)) cz, SUM(ifnull(tk,0)) tk " \
              "FROM (SELECT a.*,b.bookdate,b.lineid,b.station_number,b.equipmenttype,b.station_equipment_number,b.posoperid,b.sale_txnamtall + b.supplement_txnamtall AS sm,b.recharge_txnamtall AS cz,b.refund_txnamtall AS tk  " \
              "FROM sw_au_tvm_amount_defference a " \
              "LEFT JOIN %s_fare_statistics b " \
              " ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              " WHERE a.RECORD_TIME = %s " \
              " AND b.equipmenttype = 1 AND (b.paymethod = 0 OR b.paymethod = 1 OR b.paymethod = 10 OR  b.paymethod is NULL OR b.paymethod LIKE '%%313000000000000000000000%%') " \
              " ) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "%s_CASH_SELL_TICKET = b.sm," \
              "%s_CASH_RECHARGE = b.cz," \
              "%s_CASH_RETURN = b.tk " \
              "WHERE a.RECORD_TIME = %s AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 " \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        sqlList.append(sql)

        sql = "UPDATE sw_au_tvm_amount_defference	" \
              "SET   DIFF_MONEY = ACTUAL_MONEY - (ifnull(%s_CASH_SELL_TICKET,0) + ifnull(%s_CASH_RECHARGE,0) + ifnull(%s_CASH_RETURN,0))," \
              "SYSTEM_MONEY = (ifnull(%s_CASH_SELL_TICKET,0) +ifnull(%s_CASH_RECHARGE,0) + ifnull(%s_CASH_RETURN,0))" \
              "WHERE RECORD_TIME = %s AND  DATA_SOURCE = %s AND AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              % (typeName, typeName, typeName, typeName, typeName, typeName, self.bookdate, typeID)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def update5109For231(self):
        sqlList = []
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,SUM(amount) " \
              "FROM sc_tvm_coin_opt " \
              " WHERE bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number" % (self.bookdate)
        fc = mysql.select(sql)

        if fc is None:
            return
        for data in fc:
            sql = "UPDATE sw_au_tvm_amount_defference	" \
                  "SET   ACTUAL_MONEY =  ifnull(ACTUAL_MONEY,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = \'%s\'  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 'sc' AND AUDIT_STATUS = 0" \
                  % (decimal.Decimal(data[4]), data[1], data[2], data[3], data[0])
            sqlList.append(sql)
        mysql.executeAll(sqlList)

    def updateFinalForType232(self):

        sqlList = []
        # 20210917 Eric
        """
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,coin_number,opt_type,amount,cash_box_type " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE opt_type = 2 and  bookdate = %s " % self.bookdate

        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:

            if int(data[5]) == 2:
                sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                      "set SC_UNLOAD_AMOUNT =  ifnull(SC_UNLOAD_AMOUNT,0) + %d " \
                      " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' " \
                      "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" \
                      % (decimal.Decimal(data[6]), data[1], data[2], data[3], data[7], data[0])
                sqlList.append(sql)
            elif int(data[5]) == 1:
                sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                      "set SC_SUPPLEMNT_AMOUNT =  ifnull(SC_SUPPLEMNT_AMOUNT,0) + %d" \
                      " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' " \
                      "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
                          decimal.Decimal(data[6]), data[1], data[2], data[3], data[7], data[0])
                sqlList.append(sql)


            sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                  " SET SC_SALE_AMOUNT =  case when CASH_BOX_TYPE = 2 or CASH_BOX_TYPE = 4 then ifnull(SC_SUPPLEMNT_AMOUNT,0) - ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_HAND_OUT_AMOUNT,0)" \
                  " when CASH_BOX_TYPE = 1 or CASH_BOX_TYPE = 3  then  ifnull(SC_UNLOAD_AMOUNT,0) - ifnull(SC_SUPPLEMNT_AMOUNT,0) + ifnull(SC_HAND_OUT_AMOUNT,0) END " \
                  " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' " \
                  "AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND AUDIT_STATUS = 0" % (
                      data[1], data[2], data[3], data[7], data[0])
            sqlList.append(sql)

            sql = "UPDATE subway.sw_au_tvm_box_amount_difference " \
                  "SET SYSTEM_MONEY =  ifnull(SC_UNLOAD_AMOUNT,0)," \
                  "DIFF_MONEY =   ifnull(ACTUAL_MONEY,0)  - ifnull(SC_UNLOAD_AMOUNT,0) + ifnull(HAND_OUT_MONEY,0)" \
                  " WHERE LINE_CODE = %s AND STATION_CODE = %s AND TVM_CODE = '%s' AND CASH_BOX_TYPE = '%s' AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND  DATA_SOURCE = 2 AND AUDIT_STATUS = 0" \
                  % (data[1], data[2], data[3], data[7], data[0])
            sqlList.append(sql)
        """
        ## Eric 20211201 RIGHT JOIN 改为 LEFT JOIN
        """ 
        sql = "UPDATE subway.sw_au_tvm_box_amount_difference a " \
              "JOIN (SELECT ID, SUM(sm) sm " \
              "FROM (SELECT * " \
              "FROM subway.sw_au_tvm_box_amount_difference a " \
              "RIGHT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,opt_type,cash_box_type as cash_box_type1,amount sm " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE opt_type = 2 " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND a.CASH_BOX_TYPE = b.cash_box_type1 AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              "WHERE a.RECORD_TIME = %s AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 ) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "a.SC_UNLOAD_AMOUNT = b.sm, " \
              "a.SC_SALE_AMOUNT =  case when a.CASH_BOX_TYPE = 2 or a.CASH_BOX_TYPE = 4 then ifnull(a.SC_SUPPLEMNT_AMOUNT,0) - ifnull(b.sm,0) - ifnull(a.SC_HAND_OUT_AMOUNT,0) " \
              " when a.CASH_BOX_TYPE = 1 or a.CASH_BOX_TYPE = 3  then  ifnull(b.sm,0) - ifnull(a.SC_SUPPLEMNT_AMOUNT,0) + ifnull(a.SC_HAND_OUT_AMOUNT,0) END " \
              "WHERE a.RECORD_TIME = %s" \
              % (self.bookdate, self.bookdate)
        """
        sql = "UPDATE subway.sw_au_tvm_box_amount_difference a " \
              "JOIN (SELECT ID, SUM(ifnull(sm,0)) sm " \
              "FROM (SELECT * " \
              "FROM subway.sw_au_tvm_box_amount_difference a " \
              "LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,opt_type,cash_box_type as cash_box_type1,amount sm " \
              " FROM subway.sc_tvm_coin_opt " \
              " WHERE opt_type = 2 " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.TVM_CODE = b.station_equipment_number AND a.CASH_BOX_TYPE = b.cash_box_type1 AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              "WHERE a.RECORD_TIME = %s) c " \
              "WHERE AUDIT_STATUS = 0 AND DEL_FLAG = 0 " \
              "GROUP BY id) b ON a.ID = b.id " \
              "SET " \
              "a.SC_UNLOAD_AMOUNT = b.sm, " \
              "a.SC_SALE_AMOUNT =  case when a.CASH_BOX_TYPE = 2 or a.CASH_BOX_TYPE = 4 then ifnull(a.SC_SUPPLEMNT_AMOUNT,0) - ifnull(b.sm,0) - ifnull(a.SC_HAND_OUT_AMOUNT,0) " \
              " when a.CASH_BOX_TYPE = 1 or a.CASH_BOX_TYPE = 3  then  ifnull(b.sm,0) - ifnull(a.SC_SUPPLEMNT_AMOUNT,0) + ifnull(a.SC_HAND_OUT_AMOUNT,0) END " \
              "WHERE a.RECORD_TIME = %s  AND a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 " \
              % (self.bookdate, self.bookdate)
        sqlList.append(sql)

        sql = "UPDATE subway.sw_au_tvm_box_amount_difference a " \
              "SET SYSTEM_MONEY =  ifnull(SC_UNLOAD_AMOUNT,0)," \
              "DIFF_MONEY =   ifnull(ACTUAL_MONEY,0)  - ifnull(SC_UNLOAD_AMOUNT,0) + ifnull(HAND_OUT_MONEY,0)" \
              " WHERE a.RECORD_TIME = %s AND DATA_SOURCE = 2 AND AUDIT_STATUS = 0 AND DEL_FLAG = 0" \
              % (self.bookdate)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def updateFinalForType2314(self, typeName, typeID):

        sqlList = []

        #
        # 20210917 Eric
        """
        sql = "SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall),SUM(sale_txnamtall),SUM(supplement_txnamtall)" \
              "FROM subway.%s_fare_statistics  " \
              "WHERE equipmenttype in ('2','7')  AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              "GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid" % (
                  typeName, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_INVEST_AMOUNT = ifnull(%s_INVEST_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' " \
                  % (typeName, typeName, data[5], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_SELL_AMOUNT = ifnull(%s_SELL_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' " \
                  % (typeName, typeName, data[6], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)
            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  %s_FARETK_AMOUNT = ifnull(%s_FARETK_AMOUNT,0) + %d  " \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND FIND_IN_SET('%s',BOM_CODE)  AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' " \
                  % (typeName, typeName, data[7], data[1], data[2], data[3], data[0], data[4])
            sqlList.append(sql)

            sql = "UPDATE subway.SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
                  "SET  DIFFERENCE_AMOUNT =  PAID_AMOUNT -  MARKET_TICKET_AMOUNT - PASSENGER_WORK_AMOUNT - SINGLE_TICKET_AMOUNT - EMERGENCY_AMOUNT - (ifnull(%s_INVEST_AMOUNT,0) + ifnull(%s_SELL_AMOUNT,0) + ifnull(%s_FARETK_AMOUNT,0))," \
                  "SYS_AMOUNT = ifnull(%s_INVEST_AMOUNT,0) + ifnull(%s_SELL_AMOUNT,0) + ifnull(%s_FARETK_AMOUNT,0)" \
                  "WHERE LINE_CODE = %s AND STATION_CODE = %s AND  FIND_IN_SET('%s',BOM_CODE) AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME) AND EQUIPMENT_NUMBER = '%s' AND SYS_DATA_SOURCE = %s AND AUDIT_STATE = 0" \
                  % (typeName, typeName, typeName, typeName, typeName, typeName, data[1], data[2], data[3], data[0],
                     data[4], typeID)
            sqlList.append(sql)
        """
        #
        ## Eric 20211201 RIGHT JOIN 改为 LEFT JOIN
        """ 
        sql = "UPDATE SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "JOIN (SELECT id, SUM(sm) sm, SUM(cz) cz, SUM(tk) tk " \
              "FROM (SELECT * " \
              "FROM SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "RIGHT JOIN (SELECT bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,sale_txnamtall AS sm,recharge_txnamtall AS cz,supplement_txnamtall AS tk " \
              " FROM %s_fare_statistics " \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10 OR  paymethod is NULL OR paymethod LIKE '%%313000000000000000000000%%') " \
              ") AS b ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.EQUIPMENT_NUMBER = b.posoperid AND FIND_IN_SET(b.station_equipment_number,a.BOM_CODE) AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              " WHERE a.RECORD_TIME = %s AND a.AUDIT_STATE = 0 AND a.DEL_FLAG = 0 ) c " \
              " WHERE AUDIT_STATE = 0 AND DEL_FLAG = 0 " \
              " GROUP BY id) b ON a.ID = b.id " \
              " SET " \
              "%s_SELL_AMOUNT = b.sm," \
              "%s_INVEST_AMOUNT = b.cz," \
              "%s_FARETK_AMOUNT = b.tk " \
              " WHERE a.RECORD_TIME = %s" \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        """
        sql = "UPDATE SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "JOIN (SELECT id, SUM(ifnull(sm,0)) sm, SUM(ifnull(cz,0)) cz, SUM(ifnull(tk,0)) tk " \
              "FROM (SELECT a.* ,bookdate,lineid,station_number,equipmenttype,station_equipment_number,posoperid,sale_txnamtall AS sm,recharge_txnamtall AS cz,supplement_txnamtall AS tk " \
              "FROM SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE a " \
              "LEFT JOIN %s_fare_statistics b " \
              " ON a.LINE_CODE = b.lineid AND a.STATION_CODE = b.station_number AND a.EQUIPMENT_NUMBER = b.posoperid AND FIND_IN_SET(b.station_equipment_number,a.BOM_CODE) AND (b.bookdate BETWEEN a.INCOME_SETTLE_BEGIN_TIME AND a.INCOME_SETTLE_END_TIME) " \
              " WHERE a.RECORD_TIME = %s " \
              " AND b.equipmenttype in ('2','7') AND (b.paymethod = 0 OR b.paymethod = 1 OR b.paymethod = 10 OR  b.paymethod is NULL OR b.paymethod LIKE '%%313000000000000000000000%%') " \
              " ) c " \
              " WHERE AUDIT_STATE = 0 AND DEL_FLAG = 0 " \
              " GROUP BY id) b ON a.ID = b.id " \
              " SET " \
              "%s_SELL_AMOUNT = b.sm," \
              "%s_INVEST_AMOUNT = b.cz," \
              "%s_FARETK_AMOUNT = b.tk " \
              " WHERE a.RECORD_TIME = %s  AND a.AUDIT_STATE = 0 AND a.DEL_FLAG = 0 " \
              % (typeName, self.bookdate, typeName, typeName, typeName, self.bookdate)
        sqlList.append(sql)
        #print('测试:'+sql)
        sql = "UPDATE SW_AU_CENTER_CONDUCTOR_AMOUNT_DIFFERENCE	" \
              " SET DIFFERENCE_AMOUNT = PAID_AMOUNT - MARKET_TICKET_AMOUNT - PASSENGER_WORK_AMOUNT - SINGLE_TICKET_AMOUNT - EMERGENCY_AMOUNT - (ifnull(%s_INVEST_AMOUNT, 0) + ifnull(%s_SELL_AMOUNT, 0) + ifnull(%s_FARETK_AMOUNT, 0))," \
              "SYS_AMOUNT = ifnull(%s_INVEST_AMOUNT, 0) + ifnull(%s_SELL_AMOUNT, 0) + ifnull(%s_FARETK_AMOUNT, 0)" \
              " WHERE RECORD_TIME = %s AND  SYS_DATA_SOURCE = %s AND AUDIT_STATE = 0 AND DEL_FLAG = 0 " \
              % (typeName, typeName, typeName, typeName, typeName, typeName, self.bookdate, typeID)
        sqlList.append(sql)

        mysql.executeAll(sqlList)

    def updateFinalForType2315(self, tb1Name, tb2Name, typeStr):
        sqlList = []
        global mysql
        gol.set_value('dbName', "subway")
        #
        # 20211028 Eric for offset
        """
        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" % (
                  tb1Name, self.bookdate, tb2Name, self.bookdate, tb1Name, self.bookdate, tb2Name, self.bookdate)
        """
        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype in ('2','7') AND (paymethod = 0 OR paymethod = 1 OR paymethod = 10) AND bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" % (
                  tb1Name, self.bookdate, tb2Name, self.bookdate, tb1Name, self.bookdate, tb2Name, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            if data[14] != data[6] or data[13] != data[5] or data[15] != data[7]:

                dataList = list(data)
                index = 0
                if data[0] is None:
                    index = 8
                    dataList[5] = 0
                    dataList[6] = 0
                    dataList[7] = 0
                if data[8] is None:
                    index = 0
                    dataList[13] = 0
                    dataList[14] = 0
                    dataList[15] = 0

                # sqlTmp = "SELECT ID FROM sw_au_center_conductor_amount_difference WHERE LINE_CODE = %s AND STATION_CODE = %s AND BOM_CODE = \'BOM%s\' AND EQUIPMENT_NUMBER = %s AND (%s between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME)" % (
                # data[1 + index], data[2 + index], data[4 + index], data[5 + index], self.bookdate)
                # fctmp = mysql.select(sqlTmp)
                sqlPoser = "select user_code,nikename from sys_user where device_operate_no = '%s' and del_flag = 0" % (
                    dataList[index + 4])
                fctmp = mysql.select(sqlPoser)
                if fctmp is None or len(fctmp) == 0:
                    continue

                sql = "INSERT INTO  sw_au_conductor_hierarchy_amount_difference " \
                      "(RELATIONID,LINE_CODE,STATION_CODE,CONDUCTOR_PERSON_CODE,CONDUCTOR_PERSON_NAME,EQUIPMENT_NUMBER,BOM_CODE,COMPARATIVE_TYPE," \
                      " INVEST_AMOUNT,SELL_AMOUNT,FARETK_AMOUNT,COMPARATIVE_INVEST_AMOUNT,COMPARATIVE_SELLTK_AMOUNT,COMPARATIVE_FARETK_AMOUNT," \
                      " DIFF_INVEST_AMOUNT,DIFF_SELLTK_AMOUNT,DIFF_FARETK_AMOUNT," \
                      " ASSIST_STATE,CREATE_TIME,ASSIT_STATE,REPLACE_STATUS,SETTLE_TIME,IS_ADD_ASSIST)" \
                      " VALUES" \
                      " (%d,%s,%s,'%s','%s','%s','%s','%s',%d,%d,%d,%d,%d,%d,%d,%d,%d,%s,now(),%s,%s,%s,%s)" \
                      % (0, dataList[1 + index], dataList[2 + index], fctmp[0][0], fctmp[0][1],
                         dataList[4 + index], dataList[3 + index], typeStr, dataList[5], dataList[6], dataList[7],
                         dataList[13], dataList[14], dataList[15], dataList[5] - dataList[13],
                         dataList[6] - dataList[14], dataList[7] - dataList[15], "1", "2", "0", self.bookdate, "0")
                sqlList.append(sql)
        mysql.executeAll(sqlList)

    def updateFinalForType239(self, tb1Name, tb2Name, typeStr, typeStr2):

        # --结账单据与SC接口差异
        # sql = "insert into SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE(RELATION_SOURCE_ID,LINE_CODE,STATION_CODE," \
        #       "SETTLE_TIME,TVM_ORDER,TYPE,DEPOSIT_AMOUNT,SALE_AMOUNT,ERROR_REFUND_AMOUNT,COMPARE_TYPE,BE_DEPOSIT_AMOUNT," \
        #       "BE_SALE_AMOUNT,BE_ERROR_REFUND_AMOUNT,ASSIST_STATUS,REPLACE_STATUS,DATA_TYPE,CREATOR_ID,CREATE_TIME) " \
        #       "select a.id,a.LINE_CODE ,a.STATION_CODE,date_format('%s','%%Y-%%m-%%d'),a.TVM_CODE,%d," \
        #       "b.RECHARGE_MONEY * 100 ,b.SELL_TICKET_MONEY * 100,b.FAULT_REFUND_AMOUNT * 100,%d,sc.recharge_txnamtall,sc.sale_txnamtall,sc.refund_txnamtall,1,0,1,0,now() from sw_st_tvm_daily_bill a,sw_st_tvm_daily_bill_electronic_pay b," \
        #       "(select lineid,station_number,station_equipment_number,sum(sale_txnamtall) sale_txnamtall,sum(refund_txnamtall) refund_txnamtall,sum(recharge_txnamtall) recharge_txnamtall " \
        #       "from %s_fare_statistics where equipmenttype = '1' and paymethod in ('12','13','14','15') and bookdate = '%s' " \
        #       "GROUP BY lineid,station_number,station_equipment_number) sc where a.LINE_CODE = sc.lineid and a.STATION_CODE = sc.station_number and a.TVM_CODE = sc.station_equipment_number " \
        #       "and (date_add(date_format('%s','%%Y-%%m-%%d'), interval 12 hour) " \
        #       "BETWEEN b.START_TIME and  b.END_TIME) and a.APPROVAL_STATUS ='1' and a.DEL_FLAG= '0' and a.ID = b.RELATION_ID " \
        #       "and (ifnull(b.RECHARGE_MONEY,0)*100<>sc.recharge_txnamtall or ifnull(b.SELL_TICKET_MONEY,0)*100<>sc.sale_txnamtall or ifnull(b.FAULT_REFUND_AMOUNT,0)*100<>sc.refund_txnamtall);" \
        #       % (self.bookdate, typeid1, typeid2, typeStr, self.bookdate, self.bookdate)
        # mysql.executeAll([sql])

        sqlList = []
        global mysql
        gol.set_value('dbName', "subway")

        sql = "SELECT DISTINCT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s" \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " RIGHT  JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number" \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" \
              " UNION  " \
              " SELECT * from" \
              " (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS sc_rt,SUM(sale_txnamtall) AS sc_st,SUM(supplement_txnamtall) AS sc_sut" \
              " FROM %s " \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s" \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t1" \
              " LEFT JOIN (SELECT bookdate,lineid,station_number,station_equipment_number,posoperid,SUM(recharge_txnamtall) AS lc_rt,SUM(sale_txnamtall) AS lc_st,SUM(supplement_txnamtall) AS lc_sut" \
              " FROM %s" \
              " WHERE equipmenttype = 1 AND (paymethod = 12 OR paymethod = 13 OR paymethod = 14 OR paymethod = 15) AND bookdate = %s " \
              " GROUP BY  bookdate,lineid,station_number,station_equipment_number,posoperid) AS t2 " \
              " on t1.bookdate=t2.bookdate AND t1.lineid=t2.lineid AND t1.station_number = t2.station_number " \
              " AND t1.station_equipment_number = t2.station_equipment_number AND  t1.posoperid = t2.posoperid" % (
                  tb1Name, self.bookdate, tb2Name, self.bookdate, tb1Name, self.bookdate, tb2Name, self.bookdate)
        fc = mysql.select(sql)
        if fc is None:
            return
        for data in fc:
            if data[14] != data[6] or data[13] != data[5] or data[15] != data[7]:

                dataList = list(data)
                index = 0
                if data[0] is None:
                    index = 8
                    dataList[5] = 0
                    dataList[6] = 0
                    dataList[7] = 0
                if data[8] is None:
                    index = 0
                    dataList[13] = 0
                    dataList[14] = 0
                    dataList[15] = 0

                sql = "INSERT INTO  SW_AU_ITVM_ONLINE_AMOUNT_HIERARCHY_DIFFERENCE " \
                      "(RELATION_SOURCE_ID,LINE_CODE,STATION_CODE," \
                      "SETTLE_TIME,TVM_ORDER,TYPE," \
                      "DEPOSIT_AMOUNT,SALE_AMOUNT,ERROR_REFUND_AMOUNT,COMPARE_TYPE,BE_DEPOSIT_AMOUNT,BE_SALE_AMOUNT,BE_ERROR_REFUND_AMOUNT" \
                      ",ASSIST_STATUS,REPLACE_STATUS,DATA_TYPE,CREATOR_ID,CREATE_TIME)" \
                      " VALUES" \
                      " (%d,%s,%s,'%s','%s','%s',%d,%d,%d,%s,%d,%d,%d,%s,%s,%s,%s,now())" \
                      % (0, dataList[1 + index], dataList[2 + index], dataList[0 + index],
                         dataList[3 + index], typeStr,
                         dataList[5], dataList[6], dataList[7], typeStr2, dataList[13], dataList[14], dataList[15]
                         , "1", "2", "2", "0")
                sqlList.append(sql)
        mysql.executeAll(sqlList)

    def accTXTtoSql(self, path):
        gol.set_value('dbName', "subway")
        for root, ds, fs in os.walk(path):
            if root == path:
                for f in fs:
                    if f.find(self.bookdate + ".dat") >= 0:
                        try:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                content = fileRead.readline()

                                if content == '' or content == "\n":
                                    break
                                listV = content.replace('\n', '').split(",")
                                tmpStr = ""
                                for v in listV:
                                    tmpStr = tmpStr + "\'" + v + "\'" + ","
                                sql = "INSERT INTO subway.%s VALUES (\'%s\',%s)" % (f.split("_")[0], f, tmpStr[:-1])
                                mysql.execute(sql)

                            fileRead.close()
                            mysql.commit()
                        except Exception as e:
                            self.log.error("erro" + f + str(e))

    def txt_to_sql(self, listV, f):
        tmpStr = ""
        for v in listV:
            tmpStr = tmpStr + "\'" + v + "\'" + ","
        sql = "INSERT INTO subway.%s VALUES (\'%s\',%s)" % (f.split("_")[0], f, tmpStr[:-1])
        mysql.execute(sql)

    def alter_linecode(self, oldlineID, stationID):
        stationList14 = [41, 43, 45, 47, 49, 51, 53, 55]
        # eric 20211102 add station = 33
        # stationList54 = [21, 23, 25, 27, 29, 31, 35, 37, 39]
        stationList54 = [21, 23, 25, 27, 29, 31, 33, 35, 37, 39]
        if oldlineID == 14 and stationID in stationList54:
            return 54
        else:
            return oldlineID

    def accFaretoSql(self, path):
        gol.set_value('dbName', "subway")
        for root, ds, fs in os.walk(path):
            if root == path:
                for f in fs:
                    if f.find(self.bookdate + ".dat") >= 0:

                        if f.find("ONETPASS") >= 0:
                            ## Eric 20211110 append 5,6,35,36
                            # deviceType = [1, 2, 3, 4, 7, 31]
                            deviceType = [1, 2, 3, 4, 5, 6, 7, 31, 35, 36, 51]
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    ## Eric 20211115
                                    listV.append(self.bookdate)

                                    self.txt_to_sql(listV, f)
                                    ## Eric 20211116 追加日期，多一个数据元素
                                    """
                                    if listV is None or len(listV) != 11 or int(listV[0], 16) not in deviceType:
                                        continue
                                    """
                                    if listV is None or len(listV) != 12 or int(listV[0], 16) not in deviceType:
                                        continue

                                    mType = int(listV[3][0:2], 16)
                                    subType = int(listV[3][2:4], 16)
                                    sql = ""

                                    dictc[str(mType) + "==" + str(subType)] = "1"
                                    deviceId = listV[1][6:8]
                                    lineID = int(listV[1][2:4])
                                    stationID = int(listV[1][4:6])
                                    # 将机场城际的车站线路修改为 54
                                    # print(lineID+"--线路1"+stationID+"--车站1")
                                    lineID = self.alter_linecode(lineID, stationID)
                                    # print(lineID+"--线路2")

                                    # if listV[0] == "07" and lineID == 6:
                                    #    listV[0] = "02"

                                    if listV[0] == "01":
                                        if lineID == 1 or lineID == 4 or lineID == 5  or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "V%03d" % (int(listV[1][6:8], 16) - 60)
                                        else:
                                            deviceId = "V%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "02" or listV[0] == "33":
                                        if lineID == 1 or lineID == 4:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16) - 90)
                                        elif lineID == 5 or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16) - 190)
                                        else:
                                            deviceId = "BOM%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "03":
                                        deviceId = "STM%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "04":
                                        deviceId = "SSM%03d" % (int(listV[1][6:8], 16))
                                    if listV[0] == "07":
                                        deviceId = "BOM%03d" % (int(listV[1][6:8], 16))
                                    ##  deviceId = "ZHM%03d" % (int(listV[1][6:8], 16))
                                    ## Eric 20211116 bookdate 改用 上传日期 即 listV[10] 改为 listV[11]
                                    """ ## begin
                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics (bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], decimal.Decimal(listV[7]),
                                                  decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0)


                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (mType == 3 and subType == 16 ) or (mType == 3 and subType == 17 ) or (mType == 3 and subType == 18 ) or (mType == 3 and subType == 79 ) or (mType == 3 and subType == 80 ) or (mType == 3 and subType == 81 ):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]))
                                    """

                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics (bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], decimal.Decimal(listV[7]),
                                                  decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0, 0,
                                                  0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]), 0, 0, 0, 0)

                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (
                                            mType == 3 and subType == 16) or (mType == 3 and subType == 17) or (
                                            mType == 3 and subType == 18) or (mType == 3 and subType == 79) or (
                                            mType == 3 and subType == 80) or (mType == 3 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[11], listV[10], lineID, stationID, listV[9], listV[0],
                                                  deviceId, listV[2], listV[5], listV[6], 0 - decimal.Decimal(listV[7]),
                                                  0 - decimal.Decimal(listV[8]), 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[7]), decimal.Decimal(listV[8]))

                                    if sql != "":
                                        mysql.execute(sql)
                                    ## Eric 20211115
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_M_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s')" \
                                          % (stationID, lineID, lineID, listV[3], listV[5], listV[6], listV[9],
                                             decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')), decimal.Decimal(listV[8]).quantize(decimal.Decimal('0.0000000')), listV[10], int(listV[0], 16))
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_M_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,tradeDate,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s','%s')" \
                                          % (stationID, lineID, lineID, listV[3], listV[5], listV[6], listV[9],
                                             decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')),
                                             decimal.Decimal(listV[8]).quantize(decimal.Decimal('0.0000000')),
                                             listV[10], listV[11], int(listV[0], 16))

                                    mysql.execute(sql)

                                except Exception as err:
                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("ONECPASS") >= 0:
                            ## Eric 20211110 append 5,6,35,36
                            # deviceType = [1, 2, 3, 4, 7, 31]
                            deviceType = [1, 2, 3, 4, 5, 6, 7, 31, 35, 36, 51]
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")
                                    ## Eric 20211115
                                    listV.append(self.bookdate)

                                    self.txt_to_sql(listV, f)
                                    ## Eric 20211116 追加日期，多一个数据元素
                                    """
                                    if listV is None or len(listV) != 10 or int(listV[0][0:2], 16) not in deviceType:
                                        continue
                                    """
                                    if listV is None or len(listV) != 11 or int(listV[0][0:2], 16) not in deviceType:
                                        continue
                                    mType = int(listV[2][0:2], 16)
                                    subType = int(listV[2][2:4], 16)
                                    dictc[str(mType) + "==" + str(subType)] = "1"
                                    sql = ""
                                    deviceId = listV[0][6:8]
                                    lineID = int(listV[0][2:4])
                                    stationID = int(listV[0][4:6])
                                    lineID = self.alter_linecode(lineID, stationID)

                                    # if listV[0][0:2] == "07" and lineID == 6:
                                    #    listV[0][0:2] = "02"

                                    if listV[0][0:2] == "01":
                                        if lineID == 1 or lineID == 4 or lineID == 5 or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "V%03d" % (int(listV[0][6:8], 16) - 60)
                                        else:
                                            deviceId = "V%03d" % (int(listV[0][6:8], 16))
                                    elif listV[0][0:2] == "02":
                                        if lineID == 1 or lineID == 4:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16) - 90)
                                        elif lineID == 5 or (lineID == 10 and stationID != 53) or lineID == 16:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16) - 190)
                                        else:
                                            deviceId = "BOM%03d" % (int(listV[0][6:8], 16))
                                    if listV[0][0:2] == "03":
                                        deviceId = "STM%03d" % (int(listV[0][6:8], 16))
                                    if listV[0][0:2] == "04":
                                        deviceId = "SSM%03d" % (int(listV[0][6:8], 16))
                                    if listV[0][0:2] == "07":
                                        deviceId = "BOM%03d" % (int(listV[0][6:8], 16))
                                    ##  deviceId = "ZHM%03d" % (int(listV[0][6:8], 16))
                                    ## Eric 20211116 bookdate 改用 上传日期 即 listV[9] 改为 listV[10]
                                    """ ## begin
                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], decimal.Decimal(listV[6]),
                                                  decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0)

                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (mType == 3 and subType == 16 ) or (mType == 3 and subType == 17 ) or (mType == 3 and subType == 18 ) or (mType == 3 and subType == 79 ) or (mType == 3 and subType == 80 ) or (mType == 3 and subType == 81 ):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]))
                                    ## end
                                    """
                                    if (mType == 8 and subType == 15) or (mType == 3 and subType == 3) or (
                                            mType == 3 and subType == 2) \
                                            or (mType == 3 and subType == 1) or (mType == 3 and subType == 121):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], decimal.Decimal(listV[6]),
                                                  decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0, 0, 0)

                                    if (mType == 8 and subType == 14) or (mType == 3 and subType == 12) or (
                                            mType == 3 and subType == 11) \
                                            or (mType == 3 and subType == 10):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0)

                                    if (mType == 33 and subType == 17) or (mType == 34 and subType == 17) or (
                                            mType == 35 and subType == 17) \
                                            or (mType == 3 and subType == 120) or (mType == 3 and subType == 119) or (
                                            mType == 3 and subType == 118):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0, 0,
                                                  0)

                                    if (mType == 3 and subType == 28) or (mType == 3 and subType == 27) or (
                                            mType == 3 and subType == 26) \
                                            or (mType == 80 and subType == 1) or (mType == 81 and subType == 1) \
                                            or (mType == 1 and subType == 80) or (mType == 1 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]), 0, 0, 0, 0)

                                    if (mType == 1 and subType == 32) or (mType == 1 and subType == 33) or (
                                            mType == 3 and subType == 16) or (mType == 3 and subType == 17) or (
                                            mType == 3 and subType == 18) or (mType == 3 and subType == 79) or (
                                            mType == 3 and subType == 80) or (mType == 3 and subType == 81):
                                        sql = "INSERT INTO subway.acc_fare_statistics(bookdate,tradeDate,lineid,station_number,paymethod,equipmenttype,station_equipment_number,posoperid,productType,productSubType,sale_carnum,sale_txnamtall,supplement_carnum,supplement_txnamtall,refund_carnum,refund_txnamtall,recharge_carnum,recharge_txnamtall,offset_carnum,offset_txnamtall) VALUES ('%s','%s','%s','%s',%s,%s,'%s','%s','%s','%s',%d,%d,%d,%d,%d," \
                                              "%d,%d,%d,%d,%d)" % (
                                                  listV[10], listV[9], lineID, stationID, listV[8], listV[0][0:2],
                                                  deviceId, listV[1], listV[4], listV[5], 0 - decimal.Decimal(listV[6]),
                                                  0 - decimal.Decimal(listV[7]), 0, 0, 0, 0, 0, 0,
                                                  decimal.Decimal(listV[6]), decimal.Decimal(listV[7]))

                                    if sql != "":
                                        mysql.execute(sql)

                                    ## Eric 20211115
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_T_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s')" \
                                          % (stationID, lineID, lineID, listV[2], listV[4], listV[5], listV[8],
                                             decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000')), decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')), listV[9],int(listV[0][0:2], 16))
                                    """
                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_T_TRANS_STS (LINE_CODE,TransType,productType," \
                                          "productSubType,PamentMethod,transactionNumber,transactionValue,tradeDate,dataDate,equipmenttype) VALUES (" \
                                          "ifnull((SELECT VLINE_CODE FROM subway.sw_param_station WHERE CODE = %d AND LINE_CODE = %d ),%d), '%s', '%s', '%s', '%s', %f, %f, '%s','%s','%s')" \
                                          % (stationID, lineID, lineID, listV[2], listV[4], listV[5], listV[8],
                                             decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000')),
                                             decimal.Decimal(listV[7]).quantize(decimal.Decimal('0.0000000')), listV[9],
                                             listV[10], int(listV[0][0:2], 16))
                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("OFFSET") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 7:
                                        continue
                                    lineID = int(listV[1])
                                    stID = int(listV[2])
                                    transactionStatus = listV[3]
                                    AMOUNT = decimal.Decimal(listV[4])
                                    times = decimal.Decimal(listV[5])
                                    bookdate = listV[6]

                                    sql = "INSERT INTO subway.SW_CK_ACC_LINE_OFFSET (LINE_CODE,transactionStatus,AMOUNT," \
                                          "TIMES,dataDate) VALUES (" \
                                          "%d, '%s', %d, %d, '%s')" \
                                          % (lineID, transactionStatus, AMOUNT, times, bookdate)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("TCONSCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 8:
                                        continue
                                    productIssuerId = listV[0]
                                    CLEANTYPE = int(listV[1])
                                    SERVICEID = int(listV[2])
                                    LINECODE = int(listV[3])
                                    CleanDate = listV[4]
                                    CleanNumber = decimal.Decimal(listV[5]).quantize(decimal.Decimal('0.0000000'))
                                    CleanAmount = decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000'))
                                    bookdate = listV[7]

                                    # if 41 <= SERVICEID <= 48:
                                    #     SERVICEID = 1

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_CONS_TYPE (SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE," \
                                          "CLEAN_NUMBER,MONEY,productIssuerId) VALUES (" \
                                          "'%d', '%d','%s',ifnull((SELECT CODE FROM subway.sw_param_line WHERE OPERATOR_ID = %d ),%d),%f,%f,%s)" \
                                          % (SERVICEID, CLEANTYPE, bookdate, SERVICEID, SERVICEID, CleanNumber,
                                             CleanAmount,
                                             productIssuerId)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        elif f.find("PCONSCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 9:
                                        continue
                                    productIssuerId = int(listV[0])
                                    SERVICEID = int(listV[1])
                                    LINE_CODE = int(listV[2])
                                    productType = listV[3][0:2]
                                    productSubType = listV[3][2:4]
                                    CleanDate = listV[4]

                                    CleanNumber = decimal.Decimal(listV[5]).quantize(decimal.Decimal('0.0000000'))
                                    CleanAmount = decimal.Decimal(listV[6]).quantize(decimal.Decimal('0.0000000'))
                                    TRADTYPE = listV[7]
                                    bookdate = listV[8]

                                    # if 41 <= SERVICEID <= 48:
                                    #     SERVICEID = 1

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_CONS_TICKET (productIssuerId,SERVICE_ORG,CLEAN_TYPE,TRADE_DATE,LINE_CODE," \
                                          "CLEAN_NUMBER,MONEY,productType,productSubType) VALUES (" \
                                          "'%d','%d', '%s','%s',ifnull((SELECT CODE FROM subway.sw_param_line WHERE OPERATOR_ID = %d ),%d), %f, %f,'%s',%s)" \
                                          % (productIssuerId, SERVICEID, TRADTYPE, bookdate, SERVICEID, SERVICEID,
                                             CleanNumber, CleanAmount, productType,
                                             productSubType)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()
                        elif f.find("SALESCLEAN") >= 0:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                    if listV is None or len(listV) != 9:
                                        continue

                                    SERVICEID = listV[0]
                                    LINE_CODE = int(listV[1])
                                    productType = listV[2]
                                    CleanDate = listV[3]
                                    bili = float(listV[4])
                                    CleanNumber = decimal.Decimal(listV[5])
                                    CleanAmount = decimal.Decimal(listV[6])
                                    paymentMethod = listV[7]
                                    bookdate = listV[8]

                                    sql = "INSERT INTO subway.SW_CK_SYS_CLEANING_SALE_TICKET (SERVICE_ORG,productType,TRADE_DATE,LINE_CODE," \
                                          "MONEY,paymentMethod) VALUES (" \
                                          "'%s', '%s', '%s',%d, %d,'%s')" \
                                          % (SERVICEID, productType, bookdate, LINE_CODE, CleanAmount, paymentMethod)

                                    mysql.execute(sql)

                                except Exception as err:

                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()
                        else:
                            fileRead = open(root + "/" + f, encoding='utf-8')
                            while True:
                                try:
                                    content = fileRead.readline()

                                    if content == '':
                                        break
                                    content = content.rstrip('\n')
                                    listV = content.split(",")

                                    self.txt_to_sql(listV, f)

                                except Exception as err:
                                    self.log.error(str(f) + str(err))
                            #
                            fileRead.close()

                        mysql.commit()

                    tmpDir = self.cf.get("option", "tmpDir")
                    isExists = os.path.exists(tmpDir + self.bookdate)
                    if not isExists:
                        os.makedirs(tmpDir + self.bookdate)
                    shutil.copy(root + f, tmpDir + self.bookdate)
                    os.remove(root + f)
        self.log.info(str(dictc))

    def box5109ToSubway(self):

        # 预处理 分钱箱
        # 1456
        # sql = "DELETE FROM transaction_datasc.cash_drawer_replacement " \
        #      "WHERE operation_object != 1 " \
        #      "AND  operation_object != 3 " \
        #      "AND operation_object != 2 " \
        #      "AND operation_object != 5 " \
        #      "AND operation_object != 10"
        # mysql.executeAll([sql])

        # sql = "DELETE FROM transaction_datasc.cash_box_replacement " \
        #      "WHERE (line_number = 2 or line_number = 14 or line_number = 37) " \
        #      "and cash_box_location_id != 1 " \
        #      "AND  cash_box_location_id != 2 " \
        #      "AND cash_box_location_id != 5 " \
        #      "AND cash_box_location_id != 9 " \
        #      "AND cash_box_location_id != 10"
        # mysql.executeAll([sql])

        # sql = "DELETE FROM transaction_datasc.cash_box_replacement " \
        #      "WHERE line_number = 3 " \
        #      "and cash_box_location_id != 1 " \
        #      "AND  cash_box_location_id != 2 " \
        #      "AND cash_box_location_id != 3 " \
        #      "AND cash_box_location_id != 5 " \
        #      "AND cash_box_location_id != 7"
        # mysql.executeAll([sql])

        ##1/4/6/5/16号线
        ##小于次日0点的装入，放到当天
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(LPAD(station_number,2,0) AS SIGNED)," \
              " concat('V',LPAD(equipment_number- (case line_number when 9 then 0 else 60 end),3,0))," \
              " case when operation_object = 2 then 1 when operation_object in (5,6) then 2 when operation_object = 1 then 3 when operation_object in (3,4) then 4 when 10 then 5 end," \
              "right(cash_box_id,8)," \
              " case operation_type when 1 then 2 when 2 then 1 end,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement" \
              " where equipment_type = 1 and operation_type = 2 and operation_object in (1,2,3,5,10) and right(replacement_time,14) <='%s2359' " \
              " group by line_number,station_number,equipment_number,operation_object,cash_box_id,operation_type;" % (
                  self.bookdate, self.bookdate)
        mysql.execute(sql)

        nextDay = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=+1)).strftime("%Y%m%d")
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(LPAD(station_number,2,0) AS SIGNED)," \
              "concat('V',LPAD(equipment_number-(case line_number when 9 then 0 else 60 end),3,0))," \
              " case when operation_object = 2 then 1 when operation_object in (5,6) then 2 when operation_object = 1 then 3 when operation_object in (3,4) then 4 when 10 then 5 end," \
              "right(cash_box_id,8),1,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement" \
              " where equipment_type = 1 and operation_type = 2 and operation_object in (1,2,3,5,10) and right(replacement_time,14) >'%s2359' " \
              " group by line_number,station_number,equipment_number,operation_object,cash_box_id,operation_type;" \
              % (nextDay, self.bookdate)
        mysql.execute(sql)

        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(LPAD(station_number,2,0) AS SIGNED)," \
              "concat('V',LPAD(equipment_number-(case line_number when 9 then 0 else 60 end),3,0))," \
              " case when operation_object = 2 then 1 when operation_object in (5,6) then 2 when operation_object = 1 then 3 when operation_object in (3,4) then 4 when 10 then 5 end," \
              "right(cash_box_id,8)," \
              " case operation_type when 1 then 2 when 2 then 1 end,sum(operation_amount) coins_number" \
              " from transaction_datasc.cash_drawer_replacement " \
              " where equipment_type = 1 and operation_type = 1 and operation_object in (1,2,3,5,10) " \
              " group by line_number,station_number,equipment_number,operation_object,cash_box_id,operation_type;" \
              % (self.bookdate)
        mysql.execute(sql)

        ##2/3/14/西户号线
        ##小于次日0点的装入，放到当天
        sql = "insert into subway.sc_tvm_coin_opt_swich (bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount)" \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(right(station_number,2) AS SIGNED),concat('V',LPAD(equipment_number,3,0))," \
              " case when (((line_number = 2) and cash_box_location_id = 2) or (line_number = 3 and cash_box_location_id in (1,2)) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (4,5))) then 1 when (((line_number = 2) and cash_box_location_id = 5) or (line_number = 3 and cash_box_location_id = 3) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (2))) then 2 when (((line_number = 2) and cash_box_location_id = 1) or (line_number = 3 and cash_box_location_id = 5) or ((line_number = 14 or line_number = 37 or line_number = 36)  and cash_box_location_id in (1))) then 3 when (((line_number = 2) and cash_box_location_id = 10) or (line_number = 3 and cash_box_location_id = 7) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (7,8))) then 4 when (((line_number = 2) and cash_box_location_id = 9) or (line_number = 3 and cash_box_location_id = 6) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (6))) then 5 end," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number" \
              " from transaction_datasc.cash_box_replacement where equipment_type = 1 and change_operation_type = 1 " \
              " and right(business_data_generation_time,14) <='%s2359' " \
              "group by line_number,station_number,equipment_number,cash_box_location_id,money_box_id,change_operation_type;" \
              % (self.bookdate, self.bookdate)
        mysql.execute(sql)

        nextDay = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=+1)).strftime("%Y%m%d")
        sql = "insert into subway.sc_tvm_coin_opt_swich " \
              "(bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount) " \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(right(station_number,2) AS SIGNED),concat('V',LPAD(equipment_number,3,0))," \
              " case when (((line_number = 2) and cash_box_location_id = 2) or (line_number = 3 and cash_box_location_id in (1,2) ) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (4,5))) then 1 when (((line_number = 2) and cash_box_location_id = 5) or (line_number = 3 and cash_box_location_id = 3) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (2))) then 2 when (((line_number = 2) and cash_box_location_id = 1) or (line_number = 3 and cash_box_location_id = 5) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (1))) then 3 when (((line_number = 2) and cash_box_location_id = 10) or (line_number = 3 and cash_box_location_id = 7) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (7,8))) then 4 when (((line_number = 2) and cash_box_location_id = 9) or (line_number = 3 and cash_box_location_id = 6) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (6))) then 5 end," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number " \
              " from transaction_datasc.cash_box_replacement " \
              " where equipment_type = 1 and change_operation_type = 1 and right(business_data_generation_time,14) >'%s2359' " \
              " group by line_number,station_number,equipment_number,cash_box_location_id,money_box_id,change_operation_type;" \
              % (nextDay, self.bookdate)
        mysql.execute(sql)

        sql = "insert into subway.sc_tvm_coin_opt_swich(bookdate,lineid,station_number,station_equipment_number,cash_box_type,coin_number,opt_type,amount) " \
              " select '%s',CAST(trim(replace(line_number, '\t', '')) AS SIGNED),CAST(right(station_number,2) AS SIGNED),concat('V',LPAD(equipment_number,3,0))," \
              " case when (((line_number = 2) and cash_box_location_id = 2) or (line_number = 3 and cash_box_location_id in (1,2) ) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (4,5))) then 1 when (((line_number = 2) and cash_box_location_id = 5) or (line_number = 3 and cash_box_location_id = 3) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (2))) then 2 when (((line_number = 2) and cash_box_location_id = 1) or (line_number = 3 and cash_box_location_id = 5) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (1))) then 3 when (((line_number = 2) and cash_box_location_id = 10) or (line_number = 3 and cash_box_location_id = 7) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (7,8))) then 4 when (((line_number = 2) and cash_box_location_id = 9) or (line_number = 3 and cash_box_location_id = 6) or ((line_number = 14 or line_number = 37 or line_number = 36) and cash_box_location_id in (6))) then 5 end," \
              "case when line_number = 3 then CONCAT(SUBSTRING(right(money_box_id,8),1,4),LPAD(conv(right(money_box_id,4), 16, 10 ), 4, 0)) else  right(money_box_id,8) end ,change_operation_type,sum(coins_number)*100 coins_number " \
              " from transaction_datasc.cash_box_replacement" \
              " where equipment_type = 1 and change_operation_type = 2 " \
              " group by line_number,station_number,equipment_number,cash_box_location_id,money_box_id,change_operation_type;" \
              % (self.bookdate)
        mysql.execute(sql)

        mysql.commit()

        sql = "select bookdate,lineid,station_number,station_equipment_number,cash_box_type,opt_type,sum(amount),sum(supplement_amount) from subway.sc_tvm_coin_opt_swich group by bookdate,lineid,station_number,station_equipment_number,cash_box_type,opt_type"
        fc = mysql.select(sql)

        sqlList = []
        if fc is None:
            return

        for data in fc:
            sql = "select bookdate,CAST(trim(replace(lineid, '\t', '')) AS SIGNED),CAST(station_number AS SIGNED),station_equipment_number,cash_box_type , opt_type  from subway.sc_tvm_coin_opt where bookdate = '%s' and lineid = '%s' and station_number = '%s'" \
                  " and station_equipment_number = '%s' and cash_box_type = '%s' and opt_type = %s" \
                  % (data[0], data[1], data[2], data[3], data[4], data[5])
            fc = mysql.select(sql)
            if (fc is None) or len(fc) <= 0:
                sql = "INSERT INTO subway.sc_tvm_coin_opt (bookdate,lineid,station_number,station_equipment_number,cash_box_type ,opt_type,amount,supplement_amount)VALUES ('%s','%s','%s','%s','%s','%s',%d,0)" \
                      % (data[0], data[1], data[2], data[3], data[4], data[5], data[6])
                sqlList.append(sql)
            else:
                sql = "UPDATE subway.sc_tvm_coin_opt " \
                      " SET  amount = amount + %d  " \
                      " WHERE  bookdate = '%s' and lineid = '%s' and station_number = '%s'" \
                      " and station_equipment_number = '%s' and cash_box_type = '%s' and opt_type = %s" \
                      % (data[6], data[0], data[1], data[2], data[3], data[4], data[5])
                sqlList.append(sql)
        mysql.executeAll(sqlList)

        mysql.executeAll(["truncate table subway.sc_tvm_coin_opt_swich"])

    def out_ticket_all(self):

        sql = "INSERT ignore INTO subway.sc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
              "SELECT 0,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
              "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 1 or lineid = 4) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
              "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 7 AND (lineid = 6) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "END," \
              "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datasc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
              "AND  txnamtall = 0 AND bookdate = %s " \
              "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid " % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT ignore INTO subway.sc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
              "SELECT 1,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
              "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 1 or lineid = 4 ) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
              "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 7 AND (lineid = 6) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "END," \
              "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datasc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
              "AND  txnamtall != 0 AND bookdate = %s " \
              "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid " % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT ignore INTO subway.lc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
              "SELECT 0,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
              "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 1 or lineid = 4 ) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
              "when equipmenttype = 2 AND (lineid  = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
              "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 7 AND (lineid = 6) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "END," \
              "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datalc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
              "AND  txnamtall = 0 AND bookdate = %s " \
              "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid " % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT ignore INTO subway.lc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate)" \
              "SELECT 1,lineid,station_number,case equipmenttype when 7 then 2 when 1 then 1 when 2 then 2 end," \
              "case  when equipmenttype = 1 AND (lineid = 1 or lineid = 4 or lineid = 5 or lineid = 16) then  concat('V',LPAD(station_equipment_number-60,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 1 or lineid = 4 ) then concat('BOM',LPAD(station_equipment_number-90,3,0)) " \
              "when equipmenttype = 2 AND (lineid = 5 or lineid = 16) then concat('BOM',LPAD(station_equipment_number-190,3,0)) " \
              "when equipmenttype = 1 AND (lineid = 2 OR lineid = 3 or lineid  = 6 or lineid  = 9 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('V',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 2 AND (lineid = 2 OR lineid = 3 or lineid = 14 or lineid = 37 or lineid = 36) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "when equipmenttype = 7 AND (lineid = 6) then  concat('BOM',LPAD(station_equipment_number,3,0))" \
              "END," \
              "replace(posoperid,'\t',''),SUM(carnum),SUM(txnamtall),%s FROM  transaction_datalc.big_data_statistics WHERE udtype = 3 AND udsubtype = 121 " \
              "AND  txnamtall != 0 AND bookdate = %s " \
              "GROUP BY lineid,station_number,equipmenttype,station_equipment_number,posoperid " % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

    def out_ticket_acc(self):

        sql = "INSERT ignore INTO subway.acc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate) " \
              "SELECT TYPEt,lineid,stid,TYPE,eqnum,opid,SUM(transactionNumber),SUM(transactionValue),bookdate  FROM  " \
              "(SELECT 0 AS TYPEt, CAST(CONVERT(substr(deviceid,3,2),SIGNED) AS CHAR) AS lineid , CAST(CONVERT(substr(deviceid,5,2), SIGNED) AS CHAR) AS stid, CAST(CONVERT(substr(deviceid,1,2), SIGNED) AS CHAR) AS  type," \
              "case " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then  concat('V',LPAD( CONV(substr(deviceid,7,2), 16, 10)-60,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then concat('BOM',LPAD(CONV(substr(deviceid,7,2), 16, 10)-90,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1  then  concat('V',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0))" \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2  then  concat('BOM',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0)) END AS eqnum," \
              "operator_id AS opid,transactionNumber,transactionValue,'%s' AS bookdate " \
              "FROM  subway.onetpass WHERE udtype = '0379' AND  transactionValue = '0' AND dataDate = '%s') AS tb1 " \
              "GROUP BY TYPEt,lineid,stid,bookdate,TYPE,eqnum,opid" % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT ignore INTO subway.acc_out_ticket (tickettype,lineid,stationid,equipmenttype,station_equipment_number,posoperid,transactionNumber,transactionValue,bookdate) " \
              "SELECT TYPEt,lineid,stid,TYPE,eqnum,opid,SUM(transactionNumber),SUM(transactionValue),bookdate  FROM  " \
              "(SELECT 1 AS TYPEt, CAST(CONVERT(substr(deviceid,3,2),SIGNED) AS CHAR) AS lineid , CAST(CONVERT(substr(deviceid,5,2), SIGNED) AS CHAR) AS stid, CAST(CONVERT(substr(deviceid,1,2), SIGNED) AS CHAR) AS  type," \
              "case " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then  concat('V',LPAD( CONV(substr(deviceid,7,2), 16, 10)-60,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2 AND (CONVERT(substr(deviceid,3,2), SIGNED) = 1 OR CONVERT(substr(deviceid,3,2), SIGNED) = 4) then concat('BOM',LPAD(CONV(substr(deviceid,7,2), 16, 10)-90,3,0)) " \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 1  then  concat('V',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0))" \
              "when CONVERT(substr(deviceid,1,2), SIGNED) = 2  then  concat('BOM',LPAD(CONVERT(substr(deviceid,7,2), SIGNED),3,0)) END AS eqnum," \
              "operator_id AS opid,transactionNumber,transactionValue,'%s' AS bookdate " \
              "FROM  subway.onetpass WHERE udtype = '0379' AND  transactionValue != '0' AND dataDate = '%s') AS tb1 " \
              "GROUP BY TYPEt,lineid,stid,bookdate,TYPE,eqnum,opid" % (
                  self.bookdate, self.bookdate)

        mysql.executeAll([sql])

    def dayDataSave(self):

        date7 = (datetime.datetime.strptime(self.bookdate, "%Y%m%d") + datetime.timedelta(days=-4)).strftime("%Y%m%d")

        sql = "DELETE FROM subway.sw_ck_system_clean_view WHERE TRADE_DATE BETWEEN %s and %s" % (
            date7, self.bookdate)

        mysql.executeAll([sql])

        sql = "INSERT INTO subway.sw_ck_system_clean_view (TRADE_DATE,LINE_CODE,ONEWAY_MONEY,FACE_MONEY,QR_MONEY,SMART_CARD_MONEY,TRAFFIC_SMART_CARD_MONEY,TOTAL_MONEY) " \
              " SELECT ccc.TRADE_DATE AS TRADE_DATE,ccc.LINE_CODE AS LINE_CODE," \
              "TRUNCATE(ccc.ONEWAY_MONEY,4) AS ONEWAY_MONEY,ccc.FACE_MONEY AS FACE_MONEY,ccc.QR_MONEY AS QR_MONEY,ccc.SMART_CARD_MONEY AS SMART_CARD_MONEY,ccc.TRAFFIC_SMART_CARD_MONEY AS TRAFFIC_SMART_CARD_MONEY," \
              "TRUNCATE(((((ccc.ONEWAY_MONEY + ccc.FACE_MONEY) + ccc.QR_MONEY) + ccc.SMART_CARD_MONEY) + ccc.TRAFFIC_SMART_CARD_MONEY),2) AS TOTAL_MONEY " \
              " FROM ( SELECT a.TRADE_DATE AS TRADE_DATE,a.LINE_CODE AS LINE_CODE,((a.ONEWAY_MONEY_tmp + IFNULL(((" \
              " SELECT SUM(subway.tconsclean.CleanAmount)" \
              " FROM subway.tconsclean " \
              " WHERE ((subway.tconsclean.CLEANTYPE = 57) AND (subway.tconsclean.dataDate = (a.TRADE_DATE + INTERVAL 0 DAY)) AND (subway.tconsclean.SERVICEID = a.LINE_CODE))" \
              " GROUP BY subway.tconsclean.CLEANTYPE,subway.tconsclean.SERVICEID) / 100.00),0)) + IFNULL((( " \
              " SELECT SUM(subway.tconsclean.CleanAmount) " \
              "FROM subway.tconsclean " \
              "WHERE ((subway.tconsclean.CLEANTYPE = 56) AND (subway.tconsclean.dataDate = a.TRADE_DATE) AND (subway.tconsclean.SERVICEID = a.LINE_CODE)) " \
              " GROUP BY subway.tconsclean.CLEANTYPE,subway.tconsclean.SERVICEID) / 100.00),0)) AS ONEWAY_MONEY,a.FACE_MONEY AS FACE_MONEY,a.QR_MONEY AS QR_MONEY,a.SMART_CARD_MONEY AS SMART_CARD_MONEY,a.TRAFFIC_SMART_CARD_MONEY AS TRAFFIC_SMART_CARD_MONEY " \
              " FROM ( " \
              "SELECT subway.pconsclean.SERVICEID AS LINE_CODE,subway.pconsclean.dataDate AS TRADE_DATE, CAST((SUM(if((subway.pconsclean.productIssuerId = 99),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS SMART_CARD_MONEY, CAST((SUM(if((subway.pconsclean.productIssuerId = 90),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS TRAFFIC_SMART_CARD_MONEY, CAST((SUM(if(((subway.pconsclean.productIssuerId = 1) AND (subway.pconsclean.productType in ('1611') )),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS FACE_MONEY, CAST((SUM(if(((subway.pconsclean.productIssuerId = 1) AND ((subway.pconsclean.productType LIKE \'01%%\') OR (subway.pconsclean.productType LIKE \'02%%\') OR (subway.pconsclean.productType LIKE \'03%%\'))),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS ONEWAY_MONEY_tmp, CAST((SUM(if(((subway.pconsclean.productIssuerId = 1) AND (subway.pconsclean.productType LIKE \'16%%\')  and subway.pconsclean.productType not in('1611')),subway.pconsclean.CleanAmount,0)) / 100) AS DECIMAL(15,4)) AS QR_MONEY " \
              "FROM subway.pconsclean " \
              "WHERE (subway.pconsclean.TRADTYPE = 53 or subway.pconsclean.TRADTYPE = 0) AND subway.pconsclean.datadate BETWEEN %s and %s " \
              "GROUP BY subway.pconsclean.SERVICEID,subway.pconsclean.dataDate " \
              "ORDER BY subway.pconsclean.dataDate DESC, CAST(subway.pconsclean.SERVICEID AS signed)) a) ccc" % (
                  date7, self.bookdate)

        mysql.executeAll([sql])

    def bu_qian_xiang(self):
        sql = "update sw_st_input_cashbox_uninstalled b,sc_tvm_coin_opt a " \
              "SET b.SHOULD_BE_UNLOADED_AMOUNT = a.amount/100," \
              "b.DIFFERENCE_AMOUNT = a.amount/100 - UNINSTALL_AMOUNT " \
              "WHERE DATE_FORMAT(b.UNINSTALL_DATE,'%%Y%%m%%d') = %s AND  a.bookdate = DATE_FORMAT(b.UNINSTALL_DATE,'%%Y%%m%%d')  AND a.lineid = b.LINE_CODE AND a.station_number = b.STATION_CODE " \
              "AND a.station_equipment_number = b.TVM_CODE AND a.cash_box_type = (select value from subway.sys_dict_item where type='stm_cash_box_type' and label= b.MONEY_BOX_TYPE) AND a.opt_type = 2 " % self.bookdate
        mysql.executeAll([sql])

        sql = "update sw_au_tvm_box_amount_difference a,sw_st_input_cashbox_uninstalled b " \
              "SET a.SYSTEM_MONEY = a.SYSTEM_MONEY + b.SHOULD_BE_UNLOADED_AMOUNT," \
              "a.SC_UNLOAD_AMOUNT = a.SC_UNLOAD_AMOUNT + b.SHOULD_BE_UNLOADED_AMOUNT," \
              "DIFF_MONEY = DIFF_MONEY - b.SHOULD_BE_UNLOADED_AMOUNT" \
              " where a.AUDIT_STATUS = 0 AND a.DEL_FLAG = 0 AND b.UNINSTALL_DATE = %s and b.UNINSTALLED_DATE between INCOME_SETTLE_BEGIN_TIME and  INCOME_SETTLE_END_TIME  AND a.LINE_CODE = b.LINE_CODE AND a.STATION_CODE = b.STATION_CODE " \
              "AND a.TVM_CODE = b.TVM_CODE AND a.cash_box_type = b.MONEY_BOX_TYPE" % self.bookdate
        mysql.executeAll([sql])

    def exception_logging(self):
        # SC车站无数据判断和记录日志
        sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
              " select '%s','1','sc_fare_statistics'," \
              " b.line_code,b.code,now(),now(),'2','车站SC无数据'" \
              " from subway.sw_param_station b left join subway.sc_fare_statistics a " \
              " on b.line_code = a.lineid and b.code = a.station_number and a.bookdate = '%s' " \
              "WHERE a.station_number is null and b.IS_ACTIVE = 1" % (self.bookdate, self.bookdate)
        mysql.executeAll([sql])

        # SC车站钱箱无数据判断和记录日志
        sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
              " select '%s','1','sc_tvm_coin_opt'," \
              " b.line_code,b.code,now(),now(),'2','车站SC钱箱无数据'" \
              " from subway.sw_ck_data_submit_log l " \
              " left join subway.sw_param_station b on l.line_code = b.line_code and l.station_code = b.code and b.IS_ACTIVE = 1" \
              " left join subway.sc_tvm_coin_opt a " \
              " on l.line_code = a.lineid and l.station_code = a.station_number and a.bookdate = '%s'" \
              " WHERE '%s' between l.begin_time and l.end_time and a.station_number is null " % (
              self.bookdate, self.bookdate, self.bookdate)
        mysql.executeAll([sql])

        # LC无数据判断和记录日志
        sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
              " select '%s','2','lc_fare_statistics'," \
              " b.line_code,b.code,now(),now(),'2','车站LC无数据'" \
              " from subway.sw_param_station b left join subway.lc_fare_statistics a " \
              " on b.line_code = a.lineid and b.code = a.station_number and a.bookdate = '%s'" \
              " WHERE a.station_number is null and b.IS_ACTIVE = 1" % (self.bookdate, self.bookdate)
        mysql.executeAll([sql])

        # ACC无数据判断和记录日志
        sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
              " select '%s','3','acc_fare_statistics'," \
              " b.line_code,b.code,now(),now(),'2','车站ACC无数据'" \
              " from subway.sw_param_station b left join subway.acc_fare_statistics a " \
              " on b.line_code = a.lineid and b.code = a.station_number and a.bookdate = '%s'" \
              " WHERE a.station_number is null and b.IS_ACTIVE = 1" % (self.bookdate, self.bookdate)
        mysql.executeAll([sql])

        # ACC清分结果表判断和记录日志
        sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
              " select '%s','3','TCONSCLEAN'," \
              " b.code,'',now(),now(),'2','ACC无按类型清分数据'" \
              " from subway.sw_param_line b left join subway.SW_CK_SYS_CLEANING_CONS_TYPE a " \
              " on b.code = a.LINE_CODE and a.TRADE_DATE = '%s'" \
              " WHERE a.LINE_CODE is null and b.IS_ACTIVE = 1" % (self.bookdate, self.bookdate)
        mysql.executeAll([sql])

        sql = "insert into subway.sw_sclcacc_exception_log (TRADE_DATE,EXCEPTION_TYPE,EXCEPTION_FILE_NAME,LINEID,STATIONID,CREATE_TIME,UPDATE_TIME,ERR_TYPE,ERR_MSG) " \
              " select '%s','3','PCONSCLEAN'," \
              " b.code,'',now(),now(),'2','ACC无按票种清分数据'" \
              " from subway.sw_param_line b left join subway.SW_CK_SYS_CLEANING_CONS_TICKET a " \
              " on b.code = a.LINE_CODE and a.TRADE_DATE = '%s'" \
              " WHERE a.LINE_CODE is null and b.IS_ACTIVE = 1" % (self.bookdate, self.bookdate)
        mysql.executeAll([sql])

        # 插入通知
        sql = "insert into sw_message(TITLE,MSG_TYPE,CONTENT,STATUS,URL,CREATE_TIME,DEPT_ID) " \
              " select concat(a.TRADE_DATE,'日',case a.EXCEPTION_TYPE when '1' then concat(c.name,b.name,'SC接口异常') when '2' then concat(c.name,b.name,'LC接口异常') when '3' then 'ACC接口异常' end),'通知',case a.EXCEPTION_TYPE when '1' then concat(c.name,b.name,a.ERR_TYPE) when '2' then concat(c.name,b.name,a.ERR_TYPE) when '3' then concat('ACC接口异常:',a.ERR_TYPE) end,0,'',now(),27 " \
              " from subway.sw_sclcacc_exception_log a " \
              " left join subway.sw_param_station b on b.line_code = a.LINEID and b.code = a.STATIONID " \
              " left join subway.sw_param_line c on c.code = a.LINEID " \
              " WHERE a.TRADE_DATE = '%s' and b.IS_ACTIVE = 1 and c.IS_ACTIVE = 1 " % (self.bookdate)
        mysql.executeAll([sql])

    def mainMethod(self):

        # 清空昨日50+数据库数据
        removeLastDayFromSql()
        self.log.info("清空昨日50+数据库数据成功")

        # 解析
        self.anlysisData()
        self.log.info("解析数据成功")

        # acc 数据入库 入 subway 库
        #sourceRoot = self.cf.get("option", "source_file")
        #self.accFaretoSql(sourceRoot + "acc/")
        #self.log.info("acc 数据入库成功")

        # 归类统计 入tmp
        gol.set_value('dbName', "transaction_datasc")
        self.statisticsToSql()
        gol.set_value('dbName', "transaction_datalc")
        self.statisticsToSql()
        self.log.info("归类统计成功")

        # 2次归类统计 入subway fare库
        gol.set_value('dbName', "transaction_datasc")
        self.finalStatistics("sc_fare_statistics")
        gol.set_value('dbName', "transaction_datalc")
        self.finalStatistics("lc_fare_statistics")
        self.log.info("2次归类统计数据成功")
        #
        # # 出战入库
        self.sc_out_statistics()
        # self.update_day_cell()
        self.log.info("库存日报表日报表统计数据成功")
        # #
        # # # 数据比较差异入库
        self.updataToFinal()
        self.log.info("SC、LC数据比较差异成功")
        #self.updataToFinalACC()
        #self.log.info("ACC数据比较差异成功")
        # # #
        #
        # # 出战票统计
        self.out_ticket_all()
        #self.out_ticket_acc()

        # 日报表生成
        #self.dayDataSave()

        # 补钱箱
        self.bu_qian_xiang()

        # 异常日志
        self.exception_logging()

        # 清空源文件夹文件
        sourceRoot = self.cf.get("option", "source_file")
        del_file(sourceRoot)
        self.log.info("清空源文件夹文件成功")


if __name__ == "__main__":
    dt = DownLoadTools()
    dt.mainMethod()
